package com.apes.autoparts.parts.repository;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.apes.framework.query.ApesDao;
import com.apes.framework.util.MapUtil;
import org.springframework.stereotype.Component;

import java.util.List;
import java.util.Map;

@Component
public class PartDao extends ApesDao {


    /***
     * 查询oe所对应的配件名称
     */
    public JSONArray queryCategories(JSONArray jsonArray) {

        String sSQL = "SELECT DISTINCT B.PJMCID, B.PJMC, B.PARTNUMBER " +
                "  FROM SJZX.EPC_BBMC A, EPC_OE_PJMC B\n" +
                " WHERE A.EPC_BBMC = B.EPC_BBMC\n" +
                "   \n" +
                "   AND B.PJMCID IS NOT NULL" ;
        sSQL = sSQL + " AND B.PARTNUMBER IN ( ";
        for(int i = 0;i<jsonArray.size();i++){
            JSONObject json = jsonArray.getJSONObject(i);
            if(i == 0){
                sSQL = sSQL + "'" +json.getString("partCode")+ "'";
            }else{
                sSQL = sSQL + ",'" +json.getString("partCode")+ "'";
            }
        }
        sSQL = sSQL + ")";
        sSQL = sSQL + " AND A.QCCPMC = ";
        sSQL = sSQL + "'" +jsonArray.getJSONObject(0).getString("QCCPMC")+ "'";


        return toJSONArray(find(sSQL));
    }


    /***
     * 查询方位图主目录
     */
    public JSONArray queryMainOrientation() {
        String sSQL = "SELECT distinct T.PARENT_NAME MENU_NAME FROM EPC_ORIENTATION_MAP T ";

        return toJSONArray(find(sSQL));
    }


    /***
     * 查询方位图子目录
     */
    public JSONArray querySubOrientation(String PARENT_NAME) {

        String sSQL = "SELECT DISTINCT T.MENU_NAME FROM EPC_ORIENTATION_MAP T WHERE T.PARENT_NAME = :PARENT_NAME";

        return toJSONArray(find(sSQL, MapUtil.mapper(
                "MENU_NAME",PARENT_NAME
        )));
    }

    /***
     * 查询实际的配件名称
     */
    public JSONArray queryPJMC(String PARENT_NAME, String MENU_NAME, String selectorId) {
        String sSQL = "SELECT DISTINCT D.PJMCID ID, D.PJMC NAME\n" +
                "  FROM (SELECT A.PJMCID, T.PJMC\n" +
                "           FROM EPC_ORIENTATION_MAP A\n" +
                "          WHERE A.PARENT_NAME = :PARENT_NAME\n" +
                "            AND A.MENU_NAME = :MENU_NAME\n" +
                "            AND A.TYBJ = 1\n" +
                "         UNION ALL\n" +
                "         SELECT A.PJMCID, A.PJMC\n" +
                "           FROM EPC_ORIENTATION_MAP A, AUTO_PART B, EPC_OE_PJMC C\n" +
                "          WHERE A.PARENT_NAME = :PARENT_NAME\n" +
                "            AND A.MENU_NAME = :MENU_NAME\n" +
                "            AND A.TYBJ = 0\n" +
                "            AND B.PART_CODE = C.PARTNUMBER\n" +
                "            AND B.ORG_SELECTOR_ID = :SELECTOR_ID\n" +
                "            AND C.PJMCID = A.PJMCID\n" +
                "            AND C.BYBJ = 1) D\n" +
                " WHERE 1 = 1";

        return toJSONArray(find(sSQL, MapUtil.mapper(
                "PARENT_NAME", PARENT_NAME,
                "MENU_NAME", MENU_NAME,
                "SELECTOR_ID", selectorId
        )));
    }

    /***
     * 通过配件名称查到所有匹配的OE
     */
    public JSONArray queryPartByPJMC(String PJMCID, String selectorId) {
        String sSQL = "SELECT A.ID\n" +
                "  FROM AUTO_PART A, EPC_OE_PJMC B\n" +
                " WHERE A.PART_CODE = B.PARTNUMBER\n" +
                "   AND B.PJMCID = :PJMCID\n" +
                "   AND A.ORG_SELECTOR_ID = :SELECTOR_ID\n" +
                "   AND B.BYBJ = 1";
        return toJSONArray(find(sSQL, MapUtil.mapper(
                "PJMCID", PJMCID,
                "SELECTOR_ID", selectorId
        )));
    }

    public JSONArray queryPartOrientation(JSONArray partOrientations) {

        String sSQL = "SELECT A.* \n" +
                "   FROM SJZX.EPC_PARTNAME_FW A, SJZX.EPC_BBMC B\n" +
                "  WHERE A.EPC_BBMC = B.EPC_BBMC\n" ;
        sSQL = sSQL + " AND A.PARTNAME_ZH IN ( ";
        for(int i = 0;i<partOrientations.size();i++){
            JSONObject json = partOrientations.getJSONObject(i);

            if (json.getString("partName") != null && json.getString("partName").indexOf("'") > -1)
                json.put("partName", json.getString("partName").replace("'", "''"));
            if(i == 0){
                sSQL = sSQL + "'" +json.getString("partName")+ "'";
            }else{
                sSQL = sSQL + ",'" +json.getString("partName")+ "'";
            }
        }
        sSQL = sSQL + ")";

        sSQL = sSQL + " AND B.QCCPMC IN ( ";
        sSQL = sSQL + "'" +partOrientations.getJSONObject(0).getString("QCCPMC")+ "'";
        sSQL = sSQL + ")";

        return toJSONArray(find(sSQL));
    }

    public JSONArray queryPartEpcVersion(String QCCPMC) {
        Map queryCondition = MapUtil.mapper(
                "QCCPMC", QCCPMC
        );

        String sSQL = "SELECT EPC_BBMC\n" +
                "   FROM SJZX.EPC_BBMC B\n" +
                "  WHERE QCCPMC = :QCCPMC\n" ;
        return toJSONArray(find(sSQL, queryCondition));
    }

    public JSONArray queryOEMS(JSONArray jsonArray) {
        String sSQL = "SELECT PARTNUMBER ,OEMS   FROM  sjzx.EPC_PICTURE_OEMS   ";
        sSQL = sSQL + " WHERE PARTNUMBER IN ( ";
        for(int i = 0;i<jsonArray.size();i++){
            JSONObject json = jsonArray.getJSONObject(i);
            if(i == 0){
                sSQL = sSQL + "'" +json.getString("partCode")+ "'";
            }else{
                sSQL = sSQL + ",'" +json.getString("partCode")+ "'";
            }
        }
        sSQL = sSQL + ")";

        return toJSONArray(find(sSQL));
    }

    /***
     * 查询标准配件名称对照表（兆配）
     * @param param
     * @return
     */
    public JSONArray queryPJMCDZB_ZP(String param) {
        String sSQL = "SELECT * FROM SJZX.MAS_PJMCDZB_ZP WHERE PJMC = '" +param + "'";
        return toJSONArray(find(sSQL));
    }

    /***
     * 查询标准配件名称是否易错
     * @param param
     * @return
     */
    public JSONArray querypjmc_zptqr(JSONArray param) {
        String sSQL = "SELECT DISTINCT B.PJMCID, B.PARTNUMBER \n" +
                "  FROM SJZX.EPC_BBMC A, EPC_OE_PJMC B, SJZX.MAS_QCCXPZ C ,SJZX.MAS_PJMC_ZPTQR D " +
                "  WHERE A.EPC_BBMC = B.EPC_BBMC\n" +
                "   AND C.QCCPMC = A.QCCPMC\n" +
                "   AND B.BYBJ = 1\n" +
                "   AND B.PJMCID IS NOT NULL" +
                " AND B.PARTNUMBER IN (";
        for(int i = 0;i<param.size();i++){
            JSONObject json = param.getJSONObject(i);
            if(i == 0){
                sSQL = sSQL + "'" +json.getString("partCode")+ "'";
            }else{
                sSQL = sSQL + ",'" +json.getString("partCode")+ "'";
            }
        }
        sSQL = sSQL + ")";
        sSQL = sSQL + " AND C.PZCXID IN ( ";
        sSQL = sSQL + "'" +param.getJSONObject(0).getString("PZCXID")+ "'";
        sSQL = sSQL + ")";
        sSQL += " AND D.PJMCID = B.PJMCID ";
        return toJSONArray(find(sSQL));
    }

    /**
     * 装配图品牌白名单
     * @param param
     * @return
     */

    public JSONObject searchZPTPBFW(JSONObject param) {
        String sSQL = "SELECT * FROM SJZX.MAS_ZPT_PBFW WHERE 1=1 ";
        if (param.get("QCCPID") != null) sSQL += " AND QCCPID = :QCCPID";
        JSONObject json = new JSONObject();
        json.put("data", toJSONArray(find(sSQL, param)));
        return json;
    }
}
